package com.turing.manage.vip;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;
import com.turing.dao.Dao;
import com.turing.dao.DaoImpl;

public class VipService {
	
	Dao dao = new DaoImpl();
	
	public List<Map<String, Object>> queryExamineeAll(String option_list, String key) throws ClassNotFoundException, SQLException {
		
		if (option_list==null) {
			option_list="";
		}
		if (key==null) {
			key="";
		}
		
		System.out.println(option_list);
		
		String sql = null;
		
		if (option_list.equals("")) {
			sql = "select * from sivp";
			return dao.executeQueryForList(sql);
		}
		
		if (option_list.equals("1")) {
			sql = "select * from sivp";
			return dao.executeQueryForList(sql);
		}
		
		if (option_list.equals("2")) {
			sql = "select * from sivp where svip_style like ?";
		}
		
		if (option_list.equals("3")) {
			sql = "select * from sivp where svip_name like ?";
			
		}
		
		
		return dao.executeQueryForList(sql,	new int[]{Types.VARCHAR},new Object[]{"%"+key+"%"});
	}
	
	
	/**
	 * @desc  把导入的数据保存到数据库中
	 * @param columnValuesList
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	public void saveExamineeByImportExcel(List<String> columnValuesList) throws ClassNotFoundException, SQLException, FileNotFoundException, IOException 
	{
		System.out.println("开始写入这一行数据：\n"+columnValuesList+"\n");
		
		List<Map<String, Object>> list_examinee_ids = dao.executeQueryForList("select * from sivp where svip_id=?",new int[]{Types.VARCHAR},new Object []{columnValuesList.get(0)});
		if (list_examinee_ids.size()<=0) 
		{
			String  sql="  insert into sivp values(?,?,?,?,?,?,?)  ";
			int [] types=new int[7];
			Object []values=new Object[7];
			types[0]=Types.VARCHAR;
			types[1]=Types.VARCHAR;
			types[2]=Types.VARCHAR;
			types[3]=Types.VARCHAR;
			types[4]=Types.VARCHAR;
			types[5]=Types.VARCHAR;
			types[6]=Types.VARCHAR;
			
			
			values[0]=columnValuesList.get(0);
			values[1]=columnValuesList.get(1);
			values[2]=columnValuesList.get(2);
			values[3]=columnValuesList.get(3);
			values[4]=columnValuesList.get(4);
			values[5]=columnValuesList.get(5);
			values[6]=columnValuesList.get(6);
			

			dao.executeUpdate(sql, types, values);
		}
		
		
	}


	/**
	 *  desc 实现级联删除+批量删除
	 * @param delids
	 * @throws IOException 
	 * @throws SQLException 
	 * @throws FileNotFoundException 
	 * @throws ClassNotFoundException 
	 */
	public void deleteExamineeByIds(String[] delids) throws ClassNotFoundException, FileNotFoundException, SQLException, IOException {
		for(int i =0;i<delids.length;i++)
	     {
		     
	        //1.根据班级的id找到在这个教室考试的考生的准考证号，删除这些准考证号对应的成绩
		    String sql1=" delete from grade where examinee_id in (select examinee_id from examinee where examinee_id=?)  ";
		    //2.根据班级id删除考生信息
		    String sql2=" delete from examinee where examinee_id=?  ";
		
		    dao.executeUpdate(sql1, new int[]{Types.VARCHAR}, new Object[]{delids[i]});
		    dao.executeUpdate(sql2, new int[]{Types.VARCHAR}, new Object[]{delids[i]});

		    
		     }
		
		
	}


	public List<Map<String, Object>> queryClasss() throws ClassNotFoundException, SQLException {

		
		
		return dao.executeQueryForList("select * from classs");
	}
	
	
	
	

}
